﻿package utils;

import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Scanner;

import utils.JDBCUtil;
/**
 * 该实现类运行需要MySql数据库test和表student
 * create table student(
	id int not null primary key auto_increment,
	name varchar(20) not null
	);
	
 * @author zhrb
 *
 */
public class StudentDaoJDBCImpl implements StudentDao {

	@Override
	public boolean writeStudent(Student student) {
		Connection conn = null;
		PreparedStatement pstat = null;
		String sql = "insert into student(name) values(?) ";//表中有id和name这列
		int result = -1;
		
		try {
			conn = JDBCUtil.getConnection();
			pstat = conn.prepareStatement(sql);
			pstat.setString(1, student.getName());
			result = pstat.executeUpdate();
			
		}catch (SQLException sqle) {
			sqle.printStackTrace();
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			JDBCUtil.realeaseAll(null,pstat, conn);
		}
		return result>0?true:false;
	}

	@Override
	public Student readStudent(String name) {
		Connection conn = null;
		PreparedStatement pstat = null;
		ResultSet rs = null;
		String sql = "select * from student where name like ?";//表中有id和name这列
		Student student = null;
		try {
			conn = JDBCUtil.getConnection();
			pstat = conn.prepareStatement(sql);
			pstat.setString(1, name);
			rs = pstat.executeQuery();
			while(rs.next()){
				int stuId = rs.getInt("id");
				String stuName = rs.getString("name");
				student = new Student(stuId, stuName);
			}
		}catch (SQLException sqle) {
			sqle.printStackTrace();
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			JDBCUtil.realeaseAll(rs,pstat, conn);
		}
		return student;
	}

	@Override
	public void diplayAllStudent() {
		Connection conn = null;
		Statement stat = null;
		ResultSet rs = null;
		String sql = "select * from student";//表中有id和name这列
		try {
			conn = JDBCUtil.getConnection();
			stat = conn.createStatement();
			rs = stat.executeQuery(sql);
			while(rs.next()){
				int id = rs.getInt("id");
				String name = rs.getString("name");
				System.out.format("该学生的id=%d,姓名=%s\n",id,name);
			}
		}catch (SQLException sqle) {
			sqle.printStackTrace();
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			JDBCUtil.realeaseAll(rs,stat, conn);
		}
	}

	@Override
	public List<Student> getAllStudents() {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public int add(Student stu) {
		  Connection conn = null;  
          PreparedStatement pst = null; 
          String sql=  "insert into students(id,name) values(?,?)";
          int result=-1;
          try{  
        	  
              pst=conn.prepareStatement(sql);  
              pst.setInt(1,stu.getId());  
              pst.setString(2, stu.getName());  
               result=pst.executeUpdate();  
             
          }catch (SQLException sqle) {
  			sqle.printStackTrace();
  		}catch(Exception e){
  			e.printStackTrace();
  		}finally{
  			JDBCUtil.realeaseAll(null,pst, conn);
  		}
  		return result>0?1:-1;  
	}

	@Override
	public  int delete(int sid) {
		Connection conn = null;  
        PreparedStatement pst = null; 
        int result=-1;
       String strSql = "delete from students where id = ?";
        try {  conn = JDBCUtil.getConnection();
        	pst = conn.prepareStatement(strSql);
        	//连接数据库  
        	pst.setInt(1, sid);
        	result = pst.executeUpdate();
            //显式释放资源  
            pst.close();     
        }catch (SQLException e) {  
            // TODO Auto-generated catch block  
            e.printStackTrace();  
        }finally{  
           JDBCUtil.realeaseAll(null, pst, conn);
                }  
        
		return result>0?1:-1;  
	}


public int update(Student stu) {
		 Connection conn = null;  
		 PreparedStatement pst = null; 
		 String strSql = "update students set name = ? where Id = ?";
		 int result=-1;
         try{  
        	 conn = JDBCUtil.getConnection();
         	pst = conn.prepareStatement(strSql); 
         
         	pst.setInt(1, stu.getId());
         	pst.setString(2, stu.getName());
			result= pst.executeUpdate();  
              
         }catch(Exception e){  
             e.printStackTrace();  
         }finally{  
        	 JDBCUtil.realeaseAll(null, pst, conn);  
                 }  
          
     	return result>0?1:-1;  
	}

	@Override
	public List<Student> findAll() {
		// TODO Auto-generated method stub
		return null;
	}

	public Student findById(int id) {
		ResultSet rs = null;
		 Connection conn = null;   
         Student stu = new Student(); 
   PreparedStatement pst = null; 
        String strSql = "select * from students where Id = ?";
	      try{  conn = JDBCUtil.getConnection(); 
        	 pst=conn.prepareStatement(strSql);

             pst.setInt(1, id);
 			rs = pst.executeQuery();  
             while(rs.next()){                 
                   
                 int stuId = rs.getInt("id");
 				String stuName = rs.getString("name");
 				stu = new Student(stuId, stuName);   
                 System.out.println(stu.getId()+"     "+stu.getName());  
             }  
         }catch (SQLException sqle) {
 			sqle.printStackTrace();
 		}catch(Exception e){
 			e.printStackTrace();
 		}finally{
 			JDBCUtil.realeaseAll(rs,pst, conn);
 		}
         return stu;  
	}

	public Student  findByName(String name) {
		Connection conn = null;
		PreparedStatement pstat = null;
		ResultSet rs = null;
		String sql = "select * from student where name like ?";//表中有id和name这列
		Student student = null;
		try {
			conn = JDBCUtil.getConnection();
			pstat = conn.prepareStatement(sql);
			pstat.setString(1, name);
			rs = pstat.executeQuery();
			while(rs.next()){
				int stuId = rs.getInt("id");
				String stuName = rs.getString("name");
				student = new Student(stuId, stuName);
			}
		}catch (SQLException sqle) {
			sqle.printStackTrace();
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			JDBCUtil.realeaseAll(rs,pstat, conn);
		}
		return student;
	}






}
